{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>hotelid</th>\n",
       "      <th>accnt</th>\n",
       "      <th>sta</th>\n",
       "      <th>osta</th>\n",
       "      <th>sex</th>\n",
       "      <th>vip</th>\n",
       "      <th>i_times</th>\n",
       "      <th>nation</th>\n",
       "      <th>birth</th>\n",
       "      <th>sta_tm</th>\n",
       "      <th>...</th>\n",
       "      <th>changed</th>\n",
       "      <th>cusno_des</th>\n",
       "      <th>source_des</th>\n",
       "      <th>src_des</th>\n",
       "      <th>market_des</th>\n",
       "      <th>restype_des</th>\n",
       "      <th>channel_des</th>\n",
       "      <th>ratecode_des</th>\n",
       "      <th>payment_des</th>\n",
       "      <th>hall</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>酒店名</td>\n",
       "      <td>账号码</td>\n",
       "      <td>订单状态</td>\n",
       "      <td>原订单状态</td>\n",
       "      <td>性别</td>\n",
       "      <td>尊贵级别</td>\n",
       "      <td>入住次数</td>\n",
       "      <td>国籍</td>\n",
       "      <td>出生日期</td>\n",
       "      <td>夜审查状态</td>\n",
       "      <td>...</td>\n",
       "      <td>更新时间</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>楼号</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LXZ001</td>\n",
       "      <td>F24A170005</td>\n",
       "      <td>X</td>\n",
       "      <td>X</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>X</td>\n",
       "      <td>...</td>\n",
       "      <td>2024/1/22 14:44</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LGSS01</td>\n",
       "      <td>F2401010055</td>\n",
       "      <td>X</td>\n",
       "      <td>X</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>X</td>\n",
       "      <td>...</td>\n",
       "      <td>2024/1/4 20:55</td>\n",
       "      <td>武威市凉州区人民代表大会常务委员会</td>\n",
       "      <td>NaN</td>\n",
       "      <td>酒店直接预订</td>\n",
       "      <td>酒店协议政府客户</td>\n",
       "      <td>保留至18:00</td>\n",
       "      <td>酒店直接预订</td>\n",
       "      <td>酒店协议政府价格|Government A Rate</td>\n",
       "      <td>预付款-现金</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LAHS02</td>\n",
       "      <td>F23L290103</td>\n",
       "      <td>O</td>\n",
       "      <td>S</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CN</td>\n",
       "      <td>1992/8/5</td>\n",
       "      <td>O</td>\n",
       "      <td>...</td>\n",
       "      <td>2024/1/10 19:54</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LNJ001</td>\n",
       "      <td>F2401050612</td>\n",
       "      <td>O</td>\n",
       "      <td>O</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>CN</td>\n",
       "      <td>1986/7/21</td>\n",
       "      <td>O</td>\n",
       "      <td>...</td>\n",
       "      <td>2024/1/6 9:46</td>\n",
       "      <td>NaN</td>\n",
       "      <td>金陵微信小程序(预付)</td>\n",
       "      <td>集团微信小程序</td>\n",
       "      <td>折扣促销价</td>\n",
       "      <td>在线支付</td>\n",
       "      <td>集团微信小程序</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 58 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  hotelid        accnt   sta   osta  sex   vip i_times nation      birth  \\\n",
       "0     酒店名          账号码  订单状态  原订单状态   性别  尊贵级别    入住次数     国籍       出生日期   \n",
       "1  LXZ001   F24A170005     X      X    1     0     NaN     CN        NaN   \n",
       "2  LGSS01  F2401010055     X      X  NaN   NaN     NaN     CN        NaN   \n",
       "3  LAHS02   F23L290103     O      S    1     0     NaN     CN   1992/8/5   \n",
       "4  LNJ001  F2401050612     O      O    1   NaN     NaN     CN  1986/7/21   \n",
       "\n",
       "  sta_tm  ...          changed          cusno_des   source_des  src_des  \\\n",
       "0  夜审查状态  ...             更新时间                NaN          NaN      NaN   \n",
       "1      X  ...  2024/1/22 14:44                NaN          NaN      NaN   \n",
       "2      X  ...   2024/1/4 20:55  武威市凉州区人民代表大会常务委员会          NaN   酒店直接预订   \n",
       "3      O  ...  2024/1/10 19:54                NaN          NaN      NaN   \n",
       "4      O  ...    2024/1/6 9:46                NaN  金陵微信小程序(预付)  集团微信小程序   \n",
       "\n",
       "  market_des restype_des channel_des                ratecode_des payment_des  \\\n",
       "0        NaN         NaN         NaN                         NaN         NaN   \n",
       "1        NaN         NaN         NaN                         NaN         NaN   \n",
       "2   酒店协议政府客户    保留至18:00      酒店直接预订  酒店协议政府价格|Government A Rate      预付款-现金   \n",
       "3        NaN         NaN         NaN                         NaN         NaN   \n",
       "4      折扣促销价        在线支付     集团微信小程序                         NaN         NaN   \n",
       "\n",
       "  hall  \n",
       "0   楼号  \n",
       "1  NaN  \n",
       "2    2  \n",
       "3  NaN  \n",
       "4    A  \n",
       "\n",
       "[5 rows x 58 columns]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#一些代码如果运行不出来，可能是已经通过手动处理了，可以使用原始实验数据运行\n",
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv(\"../data/sample_merged.csv\",encoding='GBK')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>otype</th>\n",
       "      <th>type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>修改前房型代码</td>\n",
       "      <td>房型代码</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>205</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>270</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       otype  type\n",
       "0    修改前房型代码  房型代码\n",
       "205      NaN   NaN\n",
       "270      NaN   NaN"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#检查otype和type两列是否值相等，输出不相等的值\n",
    "unequal_rows_type = df[['otype','type']][df['otype']!=df['type']]\n",
    "unequal_rows_type\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [],
   "source": [
    "#删除otype列，重复值\n",
    "df.drop('otype',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>charge</th>\n",
       "      <th>credit</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>收费</td>\n",
       "      <td>费用</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  charge credit\n",
       "0     收费     费用"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#检查charge和credit两列是否值相等\n",
    "unequal_rows_charge = df[['charge','credit']][df['charge']!=df['credit']]\n",
    "unequal_rows_charge\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "#全部相同，所以删除credit列\n",
    "df.drop('credit',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "vip\n",
       "0       400\n",
       "V4       19\n",
       "V3        4\n",
       "          3\n",
       "V5        2\n",
       "尊贵级别      1\n",
       "VP        1\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#查看一下vip等级的比例\n",
    "df['vip'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "hotelid\n",
       "LNJ001    82\n",
       "XNJ001    29\n",
       "LLY001    22\n",
       "XLY001    22\n",
       "LAHS01    21\n",
       "          ..\n",
       "LNT004     5\n",
       "LXZ001     5\n",
       "LNJ007     4\n",
       "LHEN03     2\n",
       "酒店名        1\n",
       "Name: count, Length: 63, dtype: int64"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#查看一下酒店消费记录的比例\n",
    "df['hotelid'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>channel</th>\n",
       "      <th>channel_des</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>HDB</td>\n",
       "      <td>酒店直接预订</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>WMP</td>\n",
       "      <td>集团微信小程序</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>CTR</td>\n",
       "      <td>携程</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>BKG</td>\n",
       "      <td>缤客</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>TFC</td>\n",
       "      <td>天下房仓</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>WKG</td>\n",
       "      <td>上门客人</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>WMS</td>\n",
       "      <td>集团微信小程序商城|</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>168</th>\n",
       "      <td>TBA</td>\n",
       "      <td>飞猪</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>179</th>\n",
       "      <td>MTA</td>\n",
       "      <td>美团</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>231</th>\n",
       "      <td>ELO</td>\n",
       "      <td>艺龙</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>342</th>\n",
       "      <td>MED</td>\n",
       "      <td>社交媒体</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>378</th>\n",
       "      <td>OTA</td>\n",
       "      <td>线上旅行社</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>417</th>\n",
       "      <td>HMI</td>\n",
       "      <td>华民</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>481</th>\n",
       "      <td>TCH</td>\n",
       "      <td>同程旅游</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>488</th>\n",
       "      <td>TTK</td>\n",
       "      <td>抖音</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>544</th>\n",
       "      <td>GDS</td>\n",
       "      <td>全球分销系统</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>718</th>\n",
       "      <td>TPT</td>\n",
       "      <td>大都市</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    channel channel_des\n",
       "2       HDB      酒店直接预订\n",
       "4       WMP     集团微信小程序\n",
       "14      CTR          携程\n",
       "29      BKG          缤客\n",
       "45      TFC        天下房仓\n",
       "70      WKG        上门客人\n",
       "132     WMS  集团微信小程序商城|\n",
       "168     TBA          飞猪\n",
       "179     MTA          美团\n",
       "231     ELO          艺龙\n",
       "342     MED        社交媒体\n",
       "378     OTA       线上旅行社\n",
       "417     HMI          华民\n",
       "481     TCH        同程旅游\n",
       "488     TTK          抖音\n",
       "544     GDS      全球分销系统\n",
       "718     TPT         大都市"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#检查channel_des 是否和字典中的描述重复\n",
    "channels = df[['channel','channel_des']][df['channel_des'].notnull()]\n",
    "channels.drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "#暂时选择删除channel_des,后续也可以选择按照字典表进行补全保留\n",
    "df.drop('channel_des',axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "#一些删除的列，基本都没有什么实际的含义\n",
    "df.drop(['rmpoststa','rmposted'],axis=1,inplace=True)\n",
    "df.drop(['cmscode','pkgnumb','lastnumb','changed'],axis=1,inplace=True)\n",
    "df.drop(['limits','packages','cotime','coby','depby'],axis=1,inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "#生成文件\n",
    "df.to_csv('../data/new_data.csv',index=False)\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
